package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.ndp.fb.rdb.api.EcMaterielCategoryRdbService;
import com.ndp.fb.rdb.db.BatchPstAssign;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.EcMaterielCategory;
import com.ndp.fb.rdb.model.EcMaterielCategorys;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by hexin on 2016/5/24.
 */
@Service
public class EcMaterielCategoryRdbServiceImpl extends DaoTemplate implements EcMaterielCategoryRdbService {

    @Override
    public List<EcMaterielCategory> findAllCN() {
        String sql = "SELECT * from ec_materiel_category e where e.is_delete = 0  ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findAllUS() {
        String sql = "SELECT e.category_us_name as category_name ,e.* from ec_materiel_category e where e.is_delete = 0 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql,DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findSupportedCN() {
        String sql = "SELECT * " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_create = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));

    }

    @Override
    public List<EcMaterielCategory> findSupportedUS() {
        String sql = "SELECT e.category_us_name as category_name ,e.* " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_create = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findSupportTemplateFilterCN() {
        String sql = "SELECT * " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_template_filter = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findSupportTemplateFilterUS() {
        String sql = "SELECT e.category_us_name as category_name ,e.* " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_template_filter = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findCnByMaterielId(Long id) {
        String sql = "SELECT\n" +
                " DISTINCT 	emc.*\n" +
                "FROM\n" +
                "	ec_materiel_category emc,\n" +
                "	ec_materiel_categorys em,\n" +
                "	ec_template_materiel et\n" +
                "WHERE\n" +
                "	et.id = em.ec_template_materiel_id\n" +
                "AND emc.id = em.category_id\n" +
                "AND em.is_delete = 0\n" +
                "AND et.id = ?";


        return getJdbcTemplate().query(sql, new Object[]{id}, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public void save(Long tempMateId, List<EcMaterielCategorys> cids) {
        String sql = "INSERT ec_materiel_categorys (ec_template_materiel_id,category_id,parent_category_id) VALUES (?,?,?)";
        batchSaveResultId(sql, new BatchPstAssign<EcMaterielCategorys>() {
            @Override
            public void setParam(PreparedStatement ps, EcMaterielCategorys ecMaterielCategorys) throws SQLException {
                ps.setObject(1, ecMaterielCategorys.getEcTemplateMaterielId());
                ps.setObject(2, ecMaterielCategorys.getCategoryId());
                ps.setObject(3, ecMaterielCategorys.getParentCategoryId());
            }
        }, cids);
    }

    @Override
    public EcMaterielCategory findById(Long pid) {
        String sql = "SELECT * from ec_materiel_category ec where ec.id = ?";
        return findOne(sql, new Object[]{pid}, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public void deleteAllByEcTemplateMaterielId(Long ecTemplateMaterielId) {
        String sql = "DELETE from ec_materiel_categorys where ec_template_materiel_id = ?";
        getJdbcTemplate().getJdbcReadTemplate().update(sql, new Object[]{ecTemplateMaterielId});
    }

    @Override
    public List<EcMaterielCategory> findSupportMaterielFilterCN() {
        String sql = "SELECT e.* " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_materiel_filter = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public List<EcMaterielCategory> findSupportMaterielFilterUS() {
        String sql = "SELECT e.category_us_name as category_name ,e.* " +
                "from ec_materiel_category e " +
                "where e.is_delete = 0 and e.support_materiel_filter = 1 ORDER by e.pid ,e.sort";
        return getJdbcTemplate().query(sql, DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }

    @Override
    public EcMaterielCategory findByCategoryType(String CategoryType) {
        String sql = "SELECT * from ec_materiel_category ec where ec.category_type = ? and ec.is_delete = 0 ";
        return findOne(sql,new Object[]{CategoryType},DefaultRowMapper.autoRowMapper(EcMaterielCategory.class));
    }
}
